﻿using Domain.Entitys.DB;
using Domain.Entitys.Pinv;
using Domain.Entitys.Sinv;
using Domain.IRepositorys.Pinv;
using Microsoft.EntityFrameworkCore;
using Microsoft.IdentityModel.Tokens;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Numerics;
using System.Reflection.Emit;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repositorys.Pinv
{
    public class PoRepository : IPoRepository
    {
        private readonly MyDbContext myDbContext;

        public PoRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }
        /// <summary>
        /// 根据采购订单code查询采购明细
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public async Task<List<pinv_details>> Getdetailswherecode(string code)
        {
            return await myDbContext.pinv_details.Where(d => d.Po_code == code).ToListAsync();
        }

        /// <summary>
        /// 条件查询总条数
        /// </summary>
        /// <param name="Po_VendorName"></param>
        /// <param name="Po_Name"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public int GetPoCount(string Po_VendorName, string Po_Name, string proName)
        {
            IQueryable<Pinv_Po> query = myDbContext.Pinv_Po.Where(d => d.Del_Flag == '0');
            if (!string.IsNullOrEmpty(Po_VendorName))
            {
                query = query.Where(d => d.Po_Vendor_Name.Contains(Po_VendorName) || d.Po_Endor_Code.Contains(Po_VendorName));
            }
            if (!string.IsNullOrEmpty(Po_Name))
            {
                query = query.Where(d => d.Po_Name.Contains(Po_Name) || d.Po_Code.Contains(Po_Name));
            }
            if (!string.IsNullOrEmpty(proName))
            {
                var detailQuery = myDbContext.pinv_details
                    .Where(d => d.Pro_name.Contains(proName) || d.Pro_code.Contains(proName))
                    .Select(d => d.Po_Id);
                query = query.Where(p => detailQuery.Contains(p.Po_Id));
            }
            return query.Count();
        }
        /// <summary>
        /// 条件、分页查询
        /// </summary>
        /// <param name="Po_VendorName"></param>
        /// <param name="Po_Name"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public List<Po_details_Order> GetPoFYWhere(string Po_VendorName, string Po_Name, int currentPage, int pageSize, string proName)
        {
            IQueryable<Pinv_Po> query = myDbContext.Pinv_Po.Where(d => d.Del_Flag == '0').OrderByDescending(d => d.Create_Time);

            if (!string.IsNullOrEmpty(Po_VendorName))
            {
                query = query.Where(d => d.Po_Vendor_Name.Contains(Po_VendorName) || d.Po_Endor_Code.Contains(Po_VendorName));
            }
            if (!string.IsNullOrEmpty(Po_Name))
            {
                query = query.Where(d => d.Po_Name.Contains(Po_Name) || d.Po_Code.Contains(Po_Name));
            }
            if (!string.IsNullOrEmpty(proName))
            {
                var detailQuery = myDbContext.pinv_details
                    .Where(d => d.Pro_name.Contains(proName) || d.Pro_code.Contains(proName))
                    .Select(d => d.Po_Id);
                query = query.Where(p => detailQuery.Contains(p.Po_Id));
            }

            var data = query.Skip((currentPage - 1) * pageSize).Take(pageSize).Select(x => new Po_details_Order
            {
                Po_Id = x.Po_Id,
                Po_Vendor_Name = x.Po_Vendor_Name,
                Po_Endor_Code = x.Po_Endor_Code,
                Contacts_Name = x.Contacts_Name,
                Contacts_Phone = x.Contacts_Phone,
                Po_Name = x.Po_Name,
                Po_Code = x.Po_Code,
                Po_Starttime = x.Po_Starttime,
                Po_Endtime = x.Po_Endtime,
                Po_Rep_Id = x.Po_Rep_Id,
                Po_depd_id = x.Po_depd_id,
                Warehouse_Code = x.Warehouse_Code,
                Po_State = x.Po_State,
                Po_Audit = x.Po_Audit,
                Po_Audit_By = x.Po_Audit_By,
                Po_Audit_Time = x.Po_Audit_Time,
                Remark = x.Remark,
                Create_By = x.Create_By,
                Create_Time = x.Create_Time,
                Update_By = x.Update_By,
                Update_Time = x.Update_Time,
                Del_Flag = x.Del_Flag,
                Po_amount = x.Po_amount,
                Po_quantity_purchased = x.Po_quantity_purchased,
                Details = x.Details
                .Select(d => new Po_details_Order.details
                {
                    Details_id = d.Details_id,
                    Po_Id = d.Po_Id,
                    Pro_name = d.Pro_name,
                    Pro_code = d.Pro_code,
                    Pro_brand = d.Pro_brand,
                    Unitmeas_id = d.Unitmeas_id,
                    Pro_model = d.Pro_model,
                    Po_number = d.Po_number,
                    Details_monovalent = d.Details_monovalent,
                    Details_storage = d.Details_storage,
                    Pb_operator = d.Pb_operator,
                    Podet_retqty = d.Podet_retqty,
                    Po_code = d.Po_code
                }).ToList()
            }).ToList();

            return data;
        }
        /// <summary>
        /// 根据供应商编号、昵称查询信息
        /// </summary>
        /// <param name="Po_VendorName"></param>
        /// <param name="Po_EndorCode"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<Pinv_Po> GetPoWhere(string Po_VendorName)
        {
            IQueryable<Pinv_Po> query = myDbContext.Pinv_Po.Where(d => d.Del_Flag == '0');
            if (!string.IsNullOrEmpty(Po_VendorName))
            {
                query = query.Where(d => d.Po_Vendor_Name.Contains(Po_VendorName)|| d.Po_Endor_Code.Contains(Po_VendorName));
            }
            return await query.FirstAsync();
        }

        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="pinv_Po"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> PostPoAdd(Pinv_Po pinv_Po, List<pinv_details> pinv_details)
        {
            //通过 BeginTransactionAsync 开启一个异步数据库事务，确保后续的主表和明细表操作在同一事务中执行。
            //using 关键字确保事务在方法结束后自动释放（即使发生异常）。
            using var transaction =await myDbContext.Database.BeginTransactionAsync();
            try
            {
                //AddAsync 将采购订单主表实体 pinv_Po 加入数据库上下文（跟踪为 “新增” 状态)
                await myDbContext.AddAsync(pinv_Po);
                //SaveChangesAsync 异步提交变更，将主表数据写入数据库。此时，主表的自增主键（如 Po_Id）和自动生成的订单编号（如 Po_Code）会被数据库赋值给 pinv_Po 对象
                await myDbContext.SaveChangesAsync();
                foreach (var pinv in pinv_details)
                {
                    //主表保存后，其 Po_Id（主键）和 Po_Code（订单编号）已生成，通过循环为每个明细项设置这两个字段，建立明细表与主表的关联（外键关系）
                    pinv.Po_code = pinv_Po.Po_Code;// 关联主表的订单编号
                    pinv.Po_Id = pinv_Po.Po_Id;// 关联主表的主键（外键）
                }
                //AddRangeAsync 批量将明细表集合加入数据库上下文
                await myDbContext.pinv_details.AddRangeAsync(pinv_details);
                //再次调用 SaveChangesAsync 提交明细表变更，将明细数据写入数据库。
                await myDbContext.SaveChangesAsync();

                // 全部操作成功则提交事务
                await transaction.CommitAsync();
                return 1;
            }catch (Exception)
            {
                //异常回滚事务
                await transaction.RollbackAsync();
                return 0;
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="pinv_Po"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> PutPoDelete(Pinv_Po pinv_Po)
        {
            Pinv_Po po = await myDbContext.Pinv_Po.FindAsync(pinv_Po.Po_Id);
            po.Del_Flag = pinv_Po.Del_Flag;
            return await myDbContext.SaveChangesAsync();
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="pinv_Po"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> PutPoUpdate(Pinv_Po pinv_Po, List<pinv_details> pinv_details)
        {
            using var transaction = await myDbContext.Database.BeginTransactionAsync();
            try
            {
                // 1. 更新采购订单主表
                var po = await myDbContext.Pinv_Po.FirstOrDefaultAsync(d => d.Po_Id == pinv_Po.Po_Id);

                // 赋值属性
                po.Po_Vendor_Name = pinv_Po.Po_Vendor_Name;
                po.Po_Endor_Code = pinv_Po.Po_Endor_Code;
                po.Contacts_Name = pinv_Po.Contacts_Name;
                po.Contacts_Phone = pinv_Po.Contacts_Phone;
                po.Po_Name = pinv_Po.Po_Name;
                po.Po_Starttime = pinv_Po.Po_Starttime;
                po.Po_Endtime = pinv_Po.Po_Endtime;
                po.Po_Rep_Id = pinv_Po.Po_Rep_Id;
                po.Po_depd_id = pinv_Po.Po_depd_id;
                po.Warehouse_Code = pinv_Po.Warehouse_Code;
                po.Remark = pinv_Po.Remark;
                po.Update_By = pinv_Po.Update_By;
                po.Update_Time = pinv_Po.Update_Time;

                myDbContext.Update(po);
                await myDbContext.SaveChangesAsync();

                // 2. 查询数据库中的旧明细
                var oldDetails = await myDbContext.pinv_details
                    .Where(d => d.Po_code == po.Po_Code)
                    .ToListAsync();

                // 3. 识别三类记录
                var toAdd = new List<pinv_details>();      // 新增明细
                var toUpdate = new List<pinv_details>();    // 更新明细
                var toDeleteIds = new List<int>();         // 删除明细ID

                // 遍历传入的新明细列表（注意：这里应该遍历newDetails，而不是oldDetails）
                foreach (var newItem in pinv_details)
                {
                    // 3.1 新记录：无主键或主键不在旧列表中
                    if (newItem.Details_id == 0 ||
                        !oldDetails.Any(old => old.Details_id == newItem.Details_id))
                    {
                        toAdd.Add(newItem);
                    }
                    // 3.2 修改记录：主键存在且字段有变化
                    else
                    {
                        var oldItem = oldDetails.First(old => old.Details_id == newItem.Details_id);
                        if (IsModified(oldItem, newItem)) // 检查字段是否变化
                        {
                            toUpdate.Add(newItem);
                        }
                    }
                }

                // 3.3 删除记录：旧明细不在新列表中
                toDeleteIds = oldDetails
                    .Where(old => !pinv_details.Any(newItem => newItem.Details_id == old.Details_id))
                    .Select(old => old.Details_id)
                    .ToList();

                // 4. 执行差异化操作
                // 4.1 删除被移除的明细
                if (toDeleteIds.Any())
                {
                    var deleteItems = oldDetails.Where(d => toDeleteIds.Contains(d.Details_id));
                    myDbContext.RemoveRange(deleteItems);
                }

                // 4.2 更新变化的明细
                foreach (var item in toUpdate)
                {
                    myDbContext.Entry(item).State = EntityState.Modified;
                }

                // 4.3 新增明细
                foreach (var item in toAdd)
                {
                    item.Po_code = po.Po_Code; // 设置外键
                    item.Po_Id = po.Po_Id;
                    await myDbContext.AddAsync(item);
                }

                await myDbContext.SaveChangesAsync();
                await transaction.CommitAsync();
                return 1;
            }
            catch (Exception)
            {
                await transaction.RollbackAsync();
                return 0;
            }
        }


        // 辅助方法：检查字段是否被修改
        private bool IsModified(pinv_details oldItem, pinv_details newItem)
        {
            return  newItem.Po_number != oldItem.Po_number;
        }
        /// <summary>
        /// 根据code审核
        /// </summary>
        /// <param name="pinv_Po"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public Task<int> PutPoUpdateAudit(Pinv_Po pinv_Po)
        {
            Pinv_Po po = myDbContext.Pinv_Po.Find(pinv_Po.Po_Id);
            po.Po_Audit = pinv_Po.Po_Audit;
            po.Po_Audit_By = pinv_Po.Po_Audit_By;
            po.Po_Audit_Time = pinv_Po.Po_Audit_Time;
            po.Po_Audit_Remark = pinv_Po.Po_Audit_Remark;
            myDbContext.Pinv_Po.Update(po);
            return myDbContext.SaveChangesAsync();

        }

        public async Task<List<Pinv_Po>> GetPoWhere(string Po_Vendor_Name,string Po_Code,int Po_State,int Po_Rep_Id,int Po_depd_id,int currentPage,int pageSize)
        {
            IQueryable<Pinv_Po> query = myDbContext.Pinv_Po
                .Where(d => d.Del_Flag == '0' && d.Po_Audit == '1'&&d.Po_State!=9)
                .OrderByDescending(d => d.Create_Time);  // 按创建时间降序排序

            if (!string.IsNullOrEmpty(Po_Vendor_Name))
            {
                query = query.Where(d => d.Po_Endor_Code.Contains(Po_Vendor_Name));
            }
            if (!string.IsNullOrEmpty(Po_Code))
            {
                query = query.Where(d => d.Po_Code.Contains(Po_Code));
            }
            if (Po_State > -1)
            {
                query = query.Where(d => d.Po_State == Po_State);
            }
            if (Po_Rep_Id > 0)
            {
                query = query.Where(d => d.Po_Rep_Id == Po_Rep_Id);
            }
            if (Po_depd_id > 0)
            {
                query = query.Where(d => d.Po_depd_id == Po_depd_id);
            }

            return await query
                .Skip((currentPage - 1) * pageSize)
                .Take(pageSize)
                .ToListAsync();
        }

        public int GetPoWhereCount(string Po_Vendor_Name, string Po_Code, int Po_State, int Po_Rep_Id, int Po_depd_id)
        {
            IQueryable<Pinv_Po> query = myDbContext.Pinv_Po.Where(d => d.Del_Flag == '0' && d.Po_Audit == '1'&&d.Po_State!=9);
            //query = query.Where(d => d.Po_State != 3);

            if (!string.IsNullOrEmpty(Po_Vendor_Name))
            {
                query = query.Where(d => d.Po_Endor_Code.Contains(Po_Vendor_Name));
            }
            if (!string.IsNullOrEmpty(Po_Code))
            {
                query = query.Where(d => d.Po_Code.Contains(Po_Code));
            }
            if (Po_State > -1)
            {
                query = query.Where(d => d.Po_State == Po_State);
            }
            if (Po_Rep_Id > 0)
            {
                query = query.Where(d => d.Po_Rep_Id == Po_Rep_Id);
            }
            if (Po_depd_id > 0)
            {
                query = query.Where(d => d.Po_depd_id == Po_depd_id);
            }
            return query.Count();
        }

        public async Task<List<pinv_details>> GetdetailswherecodeFY(string code, string procode, int unitmeas_id, int currentPage, int pageSize)
        {
            IQueryable<pinv_details> query = myDbContext.pinv_details.Where(d => d.Po_code == code);
            if (!string.IsNullOrEmpty(procode))
            {
                query = query.Where(d => d.Pro_code.Contains(procode));
            }
            if (unitmeas_id > 0)
            {
                query = query.Where(d => d.Unitmeas_id == unitmeas_id);
            }
            return await query.Skip((currentPage - 1) * pageSize).Take(pageSize).ToListAsync();
        }

        public int GetdetailswherecodeFYCount(string code, string procode, int unitmeas_id)
        {
            IQueryable<pinv_details> query = myDbContext.pinv_details.Where(d => d.Po_code == code);
            if (!string.IsNullOrEmpty(procode))
            {
                query = query.Where(d => d.Pro_code.Contains(procode));
            }
            if (unitmeas_id > 0)
            {
                query = query.Where(d => d.Unitmeas_id == unitmeas_id);
            }
            return query.Count();
        }

        /// <summary>
        /// 根据供应商名称，采购订单名称，产品名称查询汇总
        /// </summary>
        /// <param name="Po_VendorName"></param>
        /// <param name="Po_Name"></param>
        /// <param name="proName"></param>
        /// <returns></returns>
        public List<Po_details_Order> Po_Details_OrdersSummary(string Po_VendorName, string Po_Name, string proName)
        {
            IQueryable<Pinv_Po> query = myDbContext.Pinv_Po.Where(d => d.Del_Flag == '0' && d.Po_Audit == '1');
            if (!string.IsNullOrEmpty(Po_VendorName))
            {
                query = query.Where(d => d.Po_Vendor_Name.Contains(Po_VendorName) || d.Po_Endor_Code.Contains(Po_VendorName));
            }
            if (!string.IsNullOrEmpty(Po_Name))
            {
                query = query.Where(d => d.Po_Name.Contains(Po_Name) || d.Po_Code.Contains(Po_Name));
            }
            var data = query.Select(x => new Po_details_Order
            {
                Po_Id = x.Po_Id,
                Po_Vendor_Name = x.Po_Vendor_Name,
                Po_Endor_Code = x.Po_Endor_Code,
                Contacts_Name = x.Contacts_Name,
                Contacts_Phone = x.Contacts_Phone,
                Po_Name = x.Po_Name,
                Po_Code = x.Po_Code,
                Po_Starttime = x.Po_Starttime,
                Po_Endtime = x.Po_Endtime,
                Po_Rep_Id = x.Po_Rep_Id,
                Po_depd_id = x.Po_depd_id,
                Warehouse_Code = x.Warehouse_Code,
                Po_State = x.Po_State,
                Po_Audit = x.Po_Audit,
                Po_Audit_By = x.Po_Audit_By,
                Po_Audit_Time = x.Po_Audit_Time,
                Remark = x.Remark,
                Create_By = x.Create_By,
                Create_Time = x.Create_Time,
                Update_By = x.Update_By,
                Update_Time = x.Update_Time,
                Del_Flag = x.Del_Flag,
                Details = x.Details
                .Where(d => string.IsNullOrEmpty(proName) || d.Pro_name.Contains(proName) || d.Pro_code.Contains(proName))
                .Select(d => new Po_details_Order.details
                {
                    Details_id = d.Details_id,
                    Po_Id = d.Po_Id,
                    Pro_name = d.Pro_name,
                    Pro_code = d.Pro_code,
                    Pro_brand = d.Pro_brand,
                    Unitmeas_id = d.Unitmeas_id,
                    Pro_model = d.Pro_model,
                    Po_number = d.Po_number,
                    Details_monovalent = d.Details_monovalent,
                    Details_storage = d.Details_storage,
                    Pb_operator = d.Pb_operator,
                    Podet_retqty = d.Podet_retqty,
                    Po_code = d.Po_code
                }).ToList()

            }).ToList();
            return data;
        }

        public List<Po_details_Order> GetPoFYWhereAudit(string Po_VendorName, string Po_Name, int currentPage, int pageSize)
        {
            IQueryable<Pinv_Po> query = myDbContext.Pinv_Po
                .Where(d => d.Del_Flag == '0' && d.Po_Audit == '1')
                .OrderByDescending(d => d.Create_Time);  // 按创建时间降序排序

            if (!string.IsNullOrEmpty(Po_VendorName))
            {
                query = query.Where(d => d.Po_Vendor_Name.Contains(Po_VendorName) || d.Po_Endor_Code.Contains(Po_VendorName));
            }
            if (!string.IsNullOrEmpty(Po_Name))
            {
                query = query.Where(d => d.Po_Name.Contains(Po_Name) || d.Po_Code.Contains(Po_Name));
            }

            var data = query
                .Skip((currentPage - 1) * pageSize)
                .Take(pageSize)
                .Select(x => new Po_details_Order
                {
                    Po_Id = x.Po_Id,
                    Po_Vendor_Name = x.Po_Vendor_Name,
                    Po_Endor_Code = x.Po_Endor_Code,
                    Contacts_Name = x.Contacts_Name,
                    Contacts_Phone = x.Contacts_Phone,
                    Po_Name = x.Po_Name,
                    Po_Code = x.Po_Code,
                    Po_Starttime = x.Po_Starttime,
                    Po_Endtime = x.Po_Endtime,
                    Po_Rep_Id = x.Po_Rep_Id,
                    Po_depd_id = x.Po_depd_id,
                    Warehouse_Code = x.Warehouse_Code,
                    Po_State = x.Po_State,
                    Po_Audit = x.Po_Audit,
                    Po_Audit_By = x.Po_Audit_By,
                    Po_Audit_Time = x.Po_Audit_Time,
                    Remark = x.Remark,
                    Create_By = x.Create_By,
                    Create_Time = x.Create_Time,
                    Update_By = x.Update_By,
                    Update_Time = x.Update_Time,
                    Del_Flag = x.Del_Flag,
                    Details = x.Details.Select(d => new Po_details_Order.details
                    {
                        Details_id = d.Details_id,
                        Po_Id = d.Po_Id,
                        Pro_name = d.Pro_name,
                        Pro_code = d.Pro_code,
                        Pro_brand = d.Pro_brand,
                        Unitmeas_id = d.Unitmeas_id,
                        Pro_model = d.Pro_model,
                        Po_number = d.Po_number,
                        Details_monovalent = d.Details_monovalent,
                        Details_storage = d.Details_storage,
                        Pb_operator = d.Pb_operator,
                        Podet_retqty = d.Podet_retqty,
                        Po_code = d.Po_code
                    }).ToList()
                })
                .ToList();

            return data;
        }
        //报表查询总条数，只查询审核通过的
        public int GetPoCountAudit(string Po_VendorName, string Po_Name)
        {
            IQueryable<Pinv_Po> query = myDbContext.Pinv_Po.Where(d => d.Del_Flag == '0' && d.Po_Audit == '1');
            if (!string.IsNullOrEmpty(Po_VendorName))
            {
                query = query.Where(d => d.Po_Vendor_Name.Contains(Po_VendorName));
            }
            if (!string.IsNullOrEmpty(Po_Name))
            {
                query = query.Where(d => d.Po_Name.Contains(Po_Name) || d.Po_Code.Contains(Po_Name));
            }
            return query.Count();
        }

        /// <summary>
        /// 采购订单撤销
        /// </summary>
        /// <param name="pinv_Po"></param>
        /// <returns></returns>
        public async Task<int> Pinv_PoRevokeAsync(Pinv_Po pinv_Po)
        {
            Pinv_Po pinv_Po1 = await myDbContext.Pinv_Po.FirstOrDefaultAsync(p => p.Po_Id == pinv_Po.Po_Id);
            pinv_Po1.Po_Audit = pinv_Po.Po_Audit;
            myDbContext.Update(pinv_Po1);
            return await myDbContext.SaveChangesAsync();
        }
    }
}
